package com.credit.shtl.dao.banji;/**
 * @Classname BanjiDAO
 * @Description TODO
 * @Date 2020/11/5 17:49
 * @Created by SHTL
 */

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.web.bind.annotation.PathVariable;

import javax.transaction.Transactional;
import java.util.List;
import java.util.Map;

/**
 * @InterfaceName BanjiDAO
 * @Description 班级DAO接口
 * @Author SHTL
 * @Date 2020/11/5 17:49
 * @Version 1.0
 */
public interface BanjiDAO extends JpaRepository<BanjiModel,Integer>, JpaSpecificationExecutor<BanjiModel> {


    // 根据majorname 获取该专业名称下的所有班级信息
    @Query(value = "select b.banjiid,b.banjiname from banji_table as b,major_table as m where b.majorid=m.majorid and m.majorname=:majorname",nativeQuery = true)
    List<Map<String,Object>> getBanjiByMajorname(@Param("majorname") String majorname);

    // 根据 majorid 获取该专业所有班级
    @Query(value = "select * from banji_table as a where a.majorid=:majorid",nativeQuery = true)
    List<BanjiModel> findByMajorid(@PathVariable("majorid") Integer majorid);

    // 根据 banjiid 获取 banjiname
    BanjiModel findByBanjiid(Integer banjiid);


    // 根据 banjiid 获取 所有信息，包括其它表中的关联信息
    @Query(value = "select b.ctime,b.banjiid,b.banjiname,m.majorid,m.majorname,c.collegeid,c.collegename,b.content FROM banji_table  b,major_table  m,college_table c where c.collegeid=m.collegeid and b.majorid=m.majorid and b.banjiid=:banjiid",nativeQuery = true)
    Map<String,Object> findByBanjiidA(@Param("banjiid") Integer banjiid);

    // 根据当前教师账号获取该专业下的所有班级
    @Query(value = "select * from banji_table as a where a.majorid=(select c.majorid from major_table as c where c.majorname=(select b.major FROM teacher_table as b WHERE b.zhanghu=:zhanghu))",nativeQuery = true)
    List<BanjiModel> findByZhanghu(@PathVariable("zhanghu") Integer zhanghu);





    // 根据班级id进行删除
    @Transactional
    @Modifying
    @Query(value = "delete from banji_table where banjiid=:banjiid",nativeQuery = true)
    Integer deleteByBanjiid(@Param("banjiid") Integer banjiid);


    /**
     * 部门管理（管理所有学院、专业、班级信息） 动态模糊查询
     * 查询条件：    like    学院      专业
     * **/
    // 1. 无查询条件
    @Modifying
    @Query(value = "select b.ctime,b.banjiid,b.banjiname,m.majorid,m.majorname,c.collegeid,c.collegename FROM banji_table  b,major_table  m,college_table c where c.collegeid=m.collegeid and b.majorid=m.majorid",nativeQuery = true)
    List<Map<String,Object>> findBJNO();

    // 2. 仅 like
    @Modifying
    @Query(value = "select b.ctime,b.banjiid,b.banjiname,m.majorid,m.majorname,c.collegeid,c.collegename FROM banji_table b,major_table m,college_table c where c.collegeid=m.collegeid and b.majorid=m.majorid and b.majorid=m.majorid and (b.banjiname like CONCAT('%',:like,'%') or c.collegename like CONCAT('%',:like,'%') or m.majorname like CONCAT('%',:like,'%'))",nativeQuery = true)
    List<Map<String,Object>> findBJLike(@Param("like") String like);

    // 3. 仅 学院
    @Modifying
    @Query(value = "select b.ctime,b.banjiid,b.banjiname,m.majorid,m.majorname,c.collegeid,c.collegename FROM banji_table b,major_table m,college_table c where c.collegeid=m.collegeid and b.majorid=m.majorid and c.collegename=:collegename",nativeQuery = true)
    List<Map<String,Object>> findBJCollege(@Param("collegename") String collegename);

    // 4. 仅 专业
    @Modifying
    @Query(value = "select b.ctime,b.banjiid,b.banjiname,m.majorid,m.majorname,c.collegeid,c.collegename FROM banji_table b,major_table m,college_table c where c.collegeid=m.collegeid and b.majorid=m.majorid and m.majorname=:majorname",nativeQuery = true)
    List<Map<String,Object>> findBJMajor(@Param("majorname") String majorname);

    // 5. like 和 学院
    @Modifying
    @Query(value = "select b.ctime,b.banjiid,b.banjiname,m.majorid,m.majorname,c.collegeid,c.collegename FROM banji_table b,major_table m,college_table c where c.collegeid=m.collegeid and b.majorid=m.majorid and b.majorid=m.majorid and (b.banjiname like CONCAT('%',:like,'%') or c.collegename like CONCAT('%',:like,'%') or m.majorname like CONCAT('%',:like,'%')) and c.collegename=:collegename",nativeQuery = true)
    List<Map<String,Object>> findBJLikeAndCollege(@Param("like") String like,@Param("collegename") String collegename);

    // 6. like 和 专业
    @Modifying
    @Query(value = "select b.ctime,b.banjiid,b.banjiname,m.majorid,m.majorname,c.collegeid,c.collegename FROM banji_table b,major_table m,college_table c where c.collegeid=m.collegeid and b.majorid=m.majorid and b.majorid=m.majorid and (b.banjiname like CONCAT('%',:like,'%') or c.collegename like CONCAT('%',:like,'%') or m.majorname like CONCAT('%',:like,'%')) and m.majorname=:majorname",nativeQuery = true)
    List<Map<String,Object>> findBJLikeAndMajor(@Param("like") String like,@Param("majorname") String majorname);

    // 7. 学院 和 专业
    @Modifying
    @Query(value = "select b.ctime,b.banjiid,b.banjiname,m.majorid,m.majorname,c.collegeid,c.collegename FROM banji_table b,major_table m,college_table c where c.collegeid=m.collegeid and b.majorid=m.majorid and c.collegename=:collegename and m.majorname=:majorname",nativeQuery = true)
    List<Map<String,Object>> findBJCollegeAndMajor(@Param("collegename") String collegename,@Param("majorname") String majorname);

    // 8. like 和 学院 和 专业
    @Modifying
    @Query(value = "select b.ctime,b.banjiid,b.banjiname,m.majorid,m.majorname,c.collegeid,c.collegename FROM banji_table b,major_table m,college_table c where c.collegeid=m.collegeid and b.majorid=m.majorid and b.majorid=m.majorid and (b.banjiname like CONCAT('%',:like,'%') or c.collegename like CONCAT('%',:like,'%') or m.majorname like CONCAT('%',:like,'%')) and c.collegename=:collegename and m.majorname=:majorname",nativeQuery = true)
    List<Map<String,Object>> findBJLAll(@Param("like") String like,@Param("collegename") String collegename,@Param("majorname") String majorname);













}
